#!/bin/bash

#判断是否为空值
if [ -n "$1" ] ; then
  data_date=$1
else
  data_date=`date -d '-1 days' +%F`
fi


ADS_REGION_CITY_ADS_REPORT_SQL="
INSERT OVERWRITE TABLE jtp_ads_warehouse.ads_region_city_ads_report
SELECT DT, PROVINCE, CITY, IS_INVALID_TRAFFIC, CLICK_COUNT, IMPRESSION_COUNT
FROM jtp_ads_warehouse.ads_region_city_ads_report
UNION
SELECT
    '${data_date}'AS dt
    ,if(client_province='0','未知',client_province) AS province
    ,if(client_city='0','未知',client_city) AS city
    ,if(is_invalid_traffic='','异常','正常') AS is_invalid_traffic
    ,count(if(event_type='click',ad_id,NULL)) AS click_count
    ,count(ad_id) AS impression_count
FROM jtp_ads_warehouse.dwd_ads_event_log_inc
WHERE dt='${data_date}'
AND event_type IN ('click','impression')
GROUP BY province,city,is_invalid_traffic
;
"

ADS_PLATFORM_ADS_REPORT_SQL="
INSERT OVERWRITE TABLE jtp_ads_warehouse.ads_platform_ads_report
SELECT dt, platform_id, platform_name_zh, is_invalid_traffic, click_count, impression_count
FROM jtp_ads_warehouse.ads_platform_ads_report
UNION
SELECT
    '${data_date}' AS dt
    ,platform_id
    ,platform_name_zh
     ,if(is_invalid_traffic='','异常','正常') AS is_invalid_traffic
     ,count(if(event_type='click',ad_id,NULL))AS click_count
     ,count(ad_id)AS impression_count
FROM jtp_ads_warehouse.dwd_ads_event_log_inc
WHERE dt='${data_date}'
  AND event_type IN ('click','impression')
GROUP BY platform_id,platform_name_zh,is_invalid_traffic
;
"

ADS_OS_TYPE_ADS_REPORT_SQL="
INSERT OVERWRITE TABLE jtp_ads_warehouse.ads_os_type_ads_report
SELECT dt, client_os_type, is_invalid_traffic, click_count, impression_count
FROM jtp_ads_warehouse.ads_os_type_ads_report
UNION
SELECT
    '${data_date}' AS dt
    ,if(client_os_type='','未知',client_os_type)AS client_os_type
    ,if(is_invalid_traffic='','异常','正常')AS is_invalid_traffic
    ,count(if(event_type='click',ad_id,NULL))AS click_count
    ,count(ad_id)AS impression_count
FROM jtp_ads_warehouse.dwd_ads_event_log_inc
WHERE dt='${data_date}'
AND event_type IN ('click','impression')
GROUP BY client_os_type,is_invalid_traffic
;
"


ADS_PLATFORM_HOUR_ADS_REPORT_SQL="
INSERT OVERWRITE TABLE jtp_ads_warehouse.ads_platform_hour_ads_report
SELECT dt, ad_id, ads_name, hour_str, is_invalid_traffic, click_count, impression_count
FROM jtp_ads_warehouse.ads_platform_hour_ads_report
UNION
SELECT
    '${data_date}' AS dt
     ,ad_id
     ,ads_name
     ,hour(from_unixtime(event_time/1000)) AS client_os_type
     ,if(is_invalid_traffic='','异常','正常') AS is_invalid_traffic
     ,count(if(event_type='click',ad_id,NULL))AS click_count
     ,count(ad_id)AS impression_count
FROM jtp_ads_warehouse.dwd_ads_event_log_inc
WHERE dt='${data_date}'
  AND event_type IN ('click','impression')
GROUP BY ad_id,ads_name,hour(from_unixtime(event_time/1000)),is_invalid_traffic
;
"


/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e "
${ADS_REGION_CITY_ADS_REPORT_SQL}${ADS_PLATFORM_ADS_REPORT_SQL}${ADS_OS_TYPE_ADS_REPORT_SQL}
${ADS_PLATFORM_HOUR_ADS_REPORT_SQL}
"

